﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Xml.Serialization;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ERPServices
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.cs at the Solution Explorer and start debugging.
    public class DAL : IDAL
    {
        public static void InitConnection()
        {
        }

        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        public CompositeType GetDataUsingDataContract(CompositeType composite)
        {
            if (composite == null)
            {
                throw new ArgumentNullException("composite");
            }
            if (composite.BoolValue)
            {
                composite.StringValue += "Suffix";
            }
            return composite;
        }

        public PHIEU_NHAP GetReceipt(String ReceiptID)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            PHIEU_NHAP obj = dbContext.PHIEU_NHAP.FirstOrDefault(e=>e.ID.Contains(ReceiptID));

            return obj;
        }

        public List<CHI_TIET_PHIEU_NHAP_Extend> GetReceiptItems(String ReceiptID)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            List<CHI_TIET_PHIEU_NHAP> lstItems = new List<CHI_TIET_PHIEU_NHAP>();
            List<CHI_TIET_PHIEU_NHAP_Extend> lstItemExtends = new List<CHI_TIET_PHIEU_NHAP_Extend>();

            lstItems = (from record in dbContext.CHI_TIET_PHIEU_NHAP
                       where record.ID == ReceiptID
                       select record).ToList();
            int i = 0;
            foreach (CHI_TIET_PHIEU_NHAP item in lstItems)
            {
                CHI_TIET_PHIEU_NHAP_Extend itemExtend = new CHI_TIET_PHIEU_NHAP_Extend();
                CopyObject(item, itemExtend);

                itemExtend.TenHangHoaVI = (from ChiTiet in dbContext.CHI_TIET_PHIEU_NHAP
                                        from hanghoa in dbContext.HANG_HOA
                                        where ChiTiet.HangHoa == hanghoa.ID
                                        select hanghoa.TenHangVN).FirstOrDefault();
                itemExtend.TenHangHoaEN = (from ChiTiet in dbContext.CHI_TIET_PHIEU_NHAP
                                           from hanghoa in dbContext.HANG_HOA
                                           where ChiTiet.HangHoa == hanghoa.ID
                                           select hanghoa.TenHangEN).FirstOrDefault();

                itemExtend.QuyCach = (from ChiTiet in dbContext.CHI_TIET_PHIEU_NHAP
                                           from hanghoa in dbContext.HANG_HOA
                                           where ChiTiet.HangHoa == hanghoa.ID
                                           select hanghoa.QuiCach).FirstOrDefault();

                itemExtend.Dvt = (from ChiTiet in dbContext.CHI_TIET_PHIEU_NHAP
                                      from hanghoa in dbContext.HANG_HOA
                                      where ChiTiet.HangHoa == hanghoa.ID
                                      select hanghoa.DVT).FirstOrDefault();

                itemExtend.STT = i.ToString();
                i++;

                lstItemExtends.Add(itemExtend);
            }

            return lstItemExtends;
        }

        public List<PHIEU_NHAP> GetReceipts(String searchPatern)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            List<PHIEU_NHAP> lstReceipts = new List<PHIEU_NHAP>();

            lstReceipts = (from record in dbContext.PHIEU_NHAP
                          where String.IsNullOrEmpty(searchPatern) 
                          || record.ID.ToUpper().Contains(searchPatern.ToUpper())
                          select record).ToList();

            return lstReceipts;
        }

        public List<CA_NHAN> GetCA_NHANs(String searchPatern)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            List<CA_NHAN> lstCaNhans = new List<CA_NHAN>();
            lstCaNhans = (from record in dbContext.CA_NHAN
                         where String.IsNullOrEmpty(searchPatern) 
                         || record.ID.ToUpper().Contains(searchPatern.ToUpper())
                         || record.HoTen.Contains(searchPatern)
                         select record).ToList();

            return lstCaNhans;
        }

        public List<CONG_TY> GetCONG_TYs(String searchPatern)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            List<CONG_TY> lstCongty = new List<CONG_TY>();
            lstCongty = (from record in dbContext.CONG_TY
                           where String.IsNullOrEmpty(searchPatern) 
                           ||record.ID.ToUpper().Contains(searchPatern.ToUpper())
                           || record.TenCongTy.Contains(searchPatern)
                           || record.TenGiaoDich.Contains(searchPatern)
                           select record).ToList();

            return lstCongty;
        }

        public string Query(String strTableName, string strQuery)
        {
            String str = String.Empty;

            Type type = Type.GetType(String.Format("ERPServices.{0}", strTableName));
            if (type == null) return str;

            GeneralObject generalObject = new GeneralObject();
            generalObject.TableName = strTableName;

            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();
            IEnumerable collection = dbContext.Database.SqlQuery(type, strQuery, new object[]{});
            foreach (var obj in collection)
            {
                generalObject.lstObjects.Add(obj);
            }
            

            using(MemoryStream stream = new MemoryStream())
            {
                try
                {
                    XmlSerializer serializer = new XmlSerializer(typeof(GeneralObject));
                    serializer.Serialize(stream, generalObject);
                    stream.Position = 0;
                    StreamReader reader = new StreamReader(stream);
                    str = reader.ReadToEnd();
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
                
            }

            return str;
        }

        public GeneralObject GetObjects(string strTableName, string strQuery)
        {
            String str = String.Empty;

            Type type = Type.GetType(String.Format("ERPServices.{0}", strTableName));
            if (type == null) return null;

            GeneralObject generalObject = new GeneralObject();
            generalObject.TableName = strTableName;

            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();
            IEnumerable collection = dbContext.Database.SqlQuery(type, strQuery, new object[] { });
            foreach (var obj in collection)
            {
                generalObject.lstObjects.Add(obj);
            }


            //using (MemoryStream stream = new MemoryStream())
            //{
            //    try
            //    {
            //        XmlSerializer serializer = new XmlSerializer(typeof(GeneralObject));
            //        serializer.Serialize(stream, generalObject);
            //        stream.Position = 0;
            //        StreamReader reader = new StreamReader(stream);
            //        str = reader.ReadToEnd();
            //    }
            //    catch (Exception ex)
            //    {
            //        System.Console.WriteLine(ex.Message);
            //    }

            //}

            return generalObject;
        }

        public List<THE_LOAI> GetTHE_LOAI(string strTenDanhMuc)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            DANH_MUC Danhmuc = (from record in dbContext.DANH_MUC
                          where record.Ten == strTenDanhMuc
                          select record).SingleOrDefault();

            List<THE_LOAI> lstTheLoais = (from record in dbContext.THE_LOAI
                              where record.DanhMuc == Danhmuc.ID
                              select record).ToList();

            return lstTheLoais;

        }

        public List<HANG_HOA> GetHang_Hoas(String searchPatern)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();
            
            List<HANG_HOA> lstHangHoas = new List<HANG_HOA>();
            lstHangHoas = (from record in dbContext.HANG_HOA
                           where String.IsNullOrEmpty(searchPatern) || record.ID.Contains(searchPatern)
                           || record.TenHangEN.Contains(searchPatern)
                           || record.TenHangVN.Contains(searchPatern)
                           select record).ToList();

            return lstHangHoas;
        }

        public void CopyObject(Object source, Object dest)
        {
            Type tSource = source.GetType();
            Type tDest = dest.GetType();

            PropertyInfo[] propSources = tSource.GetProperties();
            PropertyInfo[] propDests= tDest.GetProperties();

            foreach (PropertyInfo propSource in propSources)
            {
                foreach (PropertyInfo propDest in propDests)
                {
                    if (propSource.Name == propDest.Name)
                    {
                        try
                        {
                            Object obj = propSource.GetValue(source);
                            propDest.SetValue(dest, obj);
                        }
                        catch (Exception ex)
                        {
                        }
                        
                    }
                }
            }
            
        }


        public CA_NHAN GetCA_NHAN(string CaNhanID)
        {
            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            if(String.IsNullOrEmpty(CaNhanID)) return null;
            //CA_NHAN obj = dbContext.CA_NHAN.FirstOrDefault(e => e.ID.Contains(CaNhanID));
            CA_NHAN obj = (from record in dbContext.CA_NHAN
                          where record.ID.ToUpper().Contains(CaNhanID.ToUpper())
                          select record).FirstOrDefault();

            return obj;
        }

        public HANG_HOA GetHANG_HOA(string HanghoaID)
        {
            if (String.IsNullOrEmpty(HanghoaID)) return null;

            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            HANG_HOA obj = dbContext.HANG_HOA.FirstOrDefault(e => e.ID.Contains(HanghoaID));

            return obj;
        }

        public CONG_TY GetCONG_TY(string CongTyID)
        {
            if (String.IsNullOrEmpty(CongTyID)) return null;

            SG_TRADE_BASIC_LE_Entities dbContext = new SG_TRADE_BASIC_LE_Entities();

            CONG_TY obj = dbContext.CONG_TY.FirstOrDefault(e => e.ID.Contains(CongTyID));

            return obj;
        }
    }
}
